﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OpenSISDataConnection;
using OSISStudentBusinessLayer;
using System.Data;

namespace OSISStudentDataLayer
{
    /// <summary>
    /// Summary description for StudentDL
    /// </summary>
    public class StudentDL
    {
        public StudentDL()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        //public void StudentAdd(StudentBL student)
        //{



        //    String strStudentAdd = "INSERT into student(stu_id,alt_id,stu_dist_id,stu_st_id,stu_st_test_id,stu_last_nm,stu_first_nm,stu_middle_nm,stu_ssn,stu_dob,stu_med_alert_ind,last_updated,stu_504_ind,gender_id,stu_called_by_nm,hispanic_ind,language_id,eth_id,stu_med_info_id,stu_email) values(" + student.StudentID + ",'" + student.AltID + "'," + student.StuDistID + "," + student.StuStID + ",'" + student.StuSttestID + "','" + student.LName + "','" + student.FName + "','" + student.MName + "','" + student.SSN + "','" + student.BirthDate + "','" + student.StuMedAlertInD + "','" + student.LastUpdatedDate + "','" + student.Stu504InD + "','" + student.Gender + "','" + student.CommonName + "','" + student.HispanicInD + "','" + student.Language + "','" + student.Ethnicity + "','" + student.StuMedinfoID + "','" + student.StuEmail + "')";

        //    DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);

        //    DataSet ds = null;

        //    ds = DataConnection.ConnectionPath.Getdata("exec getMax stu_enr_id,student_enrollment");


        //    String strStudentEnrollment = "INSERT into student_enrollment(stu_enr_id,enr_start_dt,enr_end_dt,enr_title1_ind,next_sch_id,last_sch_id,stu_id,sch_id,schyr_id,enr_sped_ind,enr_cd_id,last_updated,enr_free_red_ind,enr_hmls_sts_ind,enr_gifted_ind,grd_lvl_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",'" + student.EnrStartDate + "'," + student.EnrEndDate + "," + student.EnrTitle1InD + ",'" + student.NextSchID + "','" + student.LastSchID + "','" + student.StudentID + "','" + student.SchoolID + "','" + student.SchyrID + "','" + student.EnrSpedInD + "','" + student.EnrCdID + "','" + student.LastUpdatedDate + "','" + student.EnrFreeRedInd + "','" + student.EnrHmlsStsInD + "','" + student.EnrGiftedInD + "'," +  student.Grade + ")";

        //    DataConnection.ConnectionPath.ExecuteCommand(strStudentEnrollment);


        //    //string customfieldsave = "update Student_Setup_Custom_Field set value='" + student.CustomfieldValue.Replace("'", "''") + "',st_id=" + student.StudentID + "  where extrafield_id='" + student.ExtrafieldId + "'";

        //    //DataConnection.ConnectionPath.ExecuteCommand(customfieldsave);

        //}


        public static string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];

        public System.Data.DataSet GetStudentId(StudentBL school)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax stu_id,student");
                return ds;
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('stu_id','student')");
                return ds;
            }
            else
            {
                DataSet ds = null;

                return ds;
            }
        }


        public System.Data.DataTable SchoolDetailsByID(StudentBL school)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select schools.*,grade_levels.grd_lvl_short_nm as MaxGrade,gl.grd_lvl_short_nm as MinGrade,look_up_data.NAME as State,lud.NAME as PostState from schools,grade_levels,grade_levels as gl,look_up_data,look_up_data as lud where grade_levels.grd_lvl_id=schools.sch_max_grade_id and gl.grd_lvl_id=schools.sch_min_grade_id and schools.sch_st=look_up_data.ISO_CODE and look_up_data.Type=2 and schools.sch_po_box_st=lud.ISO_CODE and lud.Type=2 and schools.sch_id='" + school.SchoolID + "'").Tables[0];
            return dt;
        }

        public System.Data.DataTable PopulatrRepeater(StudentBL studentcontrols)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Setup_Custom_Field where extrafield_id=" + studentcontrols.ExtrafieldId + "").Tables[0];
            return dt;
        }

        public DataSet PopulateLanguage()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from language");
            return ds;
        }

        public System.Data.DataTable PopulateCalender(StudentBL studentcalender)
        {
            DataTable dt = null;
            studentcalender.SchoolID = studentcalender.ExtrafieldId;
            dt = DataConnection.ConnectionPath.Getdata("SELECT * FROM Calender_Details WHERE Sch_Id=" + studentcalender.SchoolID + "").Tables[0];
            return dt;
        }

        public DataSet PopulateEthnicity()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from ethnicity");
            return ds;
        }

        public DataSet populatrgrade()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from grade_levels");
            return ds;
        }

        public DataSet populatrenrollmentcdn()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from enrollment_codes");
            return ds;
        }

        public DataSet Populatedynamiccontrols(StudentBL student)
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from Custom_Field_List_Item where control_id=" + student.ExtrafieldId + "");
            return ds;
        }


        public DataSet populategender()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from gender");
            return ds;
        }



        public void savecustomdata(StudentBL stadd)
        {

            string customfieldsave = "update Student_Setup_Custom_Field set value='" + stadd.CustomfieldValue.Replace("'", "''") + "',st_id=" + stadd.StudentID + "  where extrafield_id='" + stadd.ExtrafieldId + "'";

            DataConnection.ConnectionPath.ExecuteCommand(customfieldsave);

        }






        public void SaveControlOrder(StudentBL student)
        {

            if (server == "MSSQLServer")
            {


                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax Sort_Order,student_fields_order");


                String strStudentEnrollment = "INSERT into student_fields_order(Sort_Order,Field_Name,student_id,extrafield_id,value,sch_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",'" + student.FieldName + "'," + student.StudentID + "," + student.ExtrafieldId + ",'" + student.CustomfieldValue.Replace("'", "''") + "'," + student.SchoolID + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentEnrollment);

            }
            if (server == "MySQL")
            {
                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('Sort_Order','student_fields_order')");


                String strStudentEnrollment = "INSERT into student_fields_order(Sort_Order,Field_Name,student_id,extrafield_id,value,sch_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",'" + student.FieldName + "'," + student.StudentID + "," + student.ExtrafieldId + ",'" + student.CustomfieldValue.Replace("'", "''") + "'," + student.SchoolID + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentEnrollment);
            }

        }

        //----------------------------------------------Tito-----------------------------------------------//

        public System.Data.DataTable getnextcategory(StudentBL StudentAdd)
        {
            DataTable dt = null;
            object catagorylist = "";
            catagorylist = DataConnection.ConnectionPath.Getdata("select Cat_Id from Student_Setup_School_Specific_Category where Sch_Id=" + StudentAdd.SchoolID + "").Tables[0].Rows[0][0];

            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Setup_Category where Cat_Id in (" + catagorylist.ToString() + ") and Cat_Id > " + StudentAdd.CatId + "").Tables[0];
            return dt;

        }

        public System.Data.DataTable getprevcategory(StudentBL StudentAdd)
        {
            DataTable dt = null;
            object catagorylist = "";
            catagorylist = DataConnection.ConnectionPath.Getdata("select Cat_Id from Student_Setup_School_Specific_Category where Sch_Id=" + StudentAdd.SchoolID + "").Tables[0].Rows[0][0];

            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Setup_Category where Cat_Id in (" + catagorylist.ToString() + ") and Cat_Id < " + StudentAdd.CatId + " order by Cat_Id desc").Tables[0];
            return dt;

        }

        public System.Data.DataTable getschoolspecificcategory(StudentBL StudentAdd)
        {
            DataTable dt = null;
            object catagorylist = "";
            catagorylist = DataConnection.ConnectionPath.Getdata("select Cat_Id from Student_Setup_School_Specific_Category where Sch_Id=" + StudentAdd.SchoolID + "").Tables[0].Rows[0][0];

            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Setup_Category where Cat_Id in (" + catagorylist.ToString() + ")order by Short_Order asc").Tables[0];
            return dt;

        }


        public System.Data.DataTable getfieldsbycategory(StudentBL SchoolSpCat)
        {
            DataTable dtdefault = null;
            DataTable dtcustom = null;
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Setup_FormLayout_Order where Sch_Id=" + SchoolSpCat.SchoolID + " and Cat_Id=" + SchoolSpCat.CatId + "").Tables[0];
            if (dt.Rows.Count == 0)
            {


                dtdefault = DataConnection.ConnectionPath.Getdata("select SSDF.Def_Field_Id as FieldId,SSDF.field_name as FieldName,SSDF.Field_Type as FieldType,SSDFS.Status as Status,SSDF.DefaultField as Default_Custom,SSDF.Status as IsMandatory from Student_Setup_Default_Field SSDF left outer join(select * from Student_Setup_Default_Field_Status where School_Id =" + SchoolSpCat.SchoolID + " and Status='A')as SSDFS on SSDF.Def_Field_Id=SSDFS.Default_Field_Id WHERE SSDF.Cat_Id=" + SchoolSpCat.CatId + " and (SSDFS.Status!='I' or SSDFS.Status IS NULL) and (SSDFS.Field_View='V_E' or SSDFS.Field_View='E')").Tables[0];
                dtcustom = DataConnection.ConnectionPath.Getdata("select SSCF.extrafield_id as FieldId, SSCF.field_name as FieldName,SSCF.field_type_id as FieldType,SSCF.Status as Status,SSCF.CustomField as Default_Custom,SSCF.CustomField as IsMandatory from Student_Setup_Custom_Field SSCF where Category_Id=" + SchoolSpCat.CatId + " and sch_id=" + SchoolSpCat.SchoolID + " and SSCF.Status!='I' and (SSCF.Field_View='V_E' or SSCF.Field_View='E')").Tables[0];

                dtdefault.Merge(dtcustom);
                return dtdefault;

            }
            else
            {
                dtdefault = DataConnection.ConnectionPath.Getdata("select SSDF.Def_Field_Id as FieldId,SSDF.field_name as FieldName,SSDF.Field_Type as FieldType,SSDFS.Status as Status,SSDF.DefaultField as Default_Custom,SSDF.Status as IsMandatory,SSFO.* from Student_Setup_Default_Field SSDF left outer join(select * from Student_Setup_Default_Field_Status where School_Id =" + SchoolSpCat.SchoolID + " and Status='A')as SSDFS on SSDF.Def_Field_Id=SSDFS.Default_Field_Id left outer JOIN Student_Setup_FormLayout_Order SSFO on SSDF.Def_Field_Id=SSFO.Field_Id AND SSDF.DefaultField=SSFO.FL_FieldType AND SSFO.Sch_Id=" + SchoolSpCat.SchoolID + " WHERE SSDF.Cat_Id=" + SchoolSpCat.CatId + " and (SSDFS.Status!='I' or SSDFS.Status IS NULL) and (SSDFS.Field_View='V_E' or SSDFS.Field_View='E') ORDER BY SSFO.Sort_Order").Tables[0];

                dtcustom = DataConnection.ConnectionPath.Getdata("select distinct SSCF.extrafield_id as FieldId, SSCF.field_name as FieldName,SSCF.field_type_id as FieldType,SSCF.Status as Status,SSCF.CustomField as Default_Custom,SSFO.* from Student_Setup_Custom_Field SSCF left outer join Student_Setup_FormLayout_Order SSFO on SSCF.extrafield_id=SSFO.Field_Id AND SSCF.Category_Id=SSFO.Cat_Id and SSCF.sch_id=SSFO.Sch_Id and SSCF.CustomField=SSFO.FL_FieldType where Category_Id=" + SchoolSpCat.CatId + " and SSCF.sch_id=" + SchoolSpCat.SchoolID + " and SSCF.Status!='I' and (SSCF.Field_View='V_E' or SSCF.Field_View='E') order by SSFO.Sort_Order").Tables[0];
                dtdefault.Merge(dtcustom);
                DataView myDataView = dtdefault.DefaultView;
                myDataView.Sort = "Sort_Order";

                return myDataView.ToTable();
                //return dtdefault;


            }
        }



        //public System.Data.DataTable getfieldsbycategory(StudentBL StudentAdd)
        //{
        //    DataTable dtdefault = null;
        //    DataTable dtcustom = null;


        //    dtdefault = DataConnection.ConnectionPath.Getdata("select SSDF.Def_Field_Id as FieldId,SSDF.field_name as FieldName,SSDF.Field_Type as FieldType,SSDFS.Status as Status,SSDF.DefaultField as Default_Custom,SSDF.Status as IsMandatory from Student_Setup_Default_Field SSDF left outer join(select * from Student_Setup_Default_Field_Status where School_Id =" + StudentAdd.SchoolID + ")as SSDFS on SSDF.Def_Field_Id=SSDFS.Default_Field_Id WHERE SSDF.Cat_Id=" + StudentAdd.CatId + " and (SSDFS.Status!='I' or SSDFS.Status IS NULL)").Tables[0];



        //    dtcustom = DataConnection.ConnectionPath.Getdata("select SSCF.extrafield_id as FieldId, SSCF.field_name as FieldName,SSCF.field_type_id as FieldType,SSCF.Status as Status,SSCF.CustomField as Default_Custom from Student_Setup_Custom_Field  SSCF where Category_Id=" + StudentAdd.CatId + " and sch_id=" + StudentAdd.SchoolID + " and SSCF.Status!='I'").Tables[0];
        //    dtdefault.Merge(dtcustom);

        //    return dtdefault;

        //}

        public System.Data.DataTable getfirstcategory(StudentBL StudentAdd)
        {
            DataTable dt = null;
            object catagorylist = "";
            catagorylist = DataConnection.ConnectionPath.Getdata("select Cat_Id from Student_Setup_School_Specific_Category where Sch_Id=" + StudentAdd.SchoolID + "").Tables[0].Rows[0][0];

            dt = DataConnection.ConnectionPath.Getdata("select min(Cat_Id) from Student_Setup_Category where Cat_Id in (" + catagorylist.ToString() + ")").Tables[0];
            return dt;

        }

        public int checkaddressfield(StudentBL fieldcount)
        {
            object count;
            count = DataConnection.ConnectionPath.Getdata("select COUNT(*) from Student_Setup_Address_Field where Def_Field_Id =" + fieldcount.FieldId + "").Tables[0].Rows[0][0];
            return Convert.ToInt32(count);
        }


        public System.Data.DataTable getaddressfieldsbyfieldid(StudentBL AddressFields)
        {



            DataTable dtdefault = null;
            // DataTable dtadditional = null;
            // DataTable ConList = null;
            object contactidlist = "";
            dtdefault = DataConnection.ConnectionPath.Getdata("select SSAF.Add_Id as FieldId,SSAF.Field_name as FieldName,SSAF.Field_Type as FieldType,SSADFS.Status as Status,SSAF.Status as IsMandatory,SSDF.DefaultField as Default_Custom,SSADFS.Field_View from Student_Setup_Address_Field SSAF left outer join(select * from Student_Setup_Address_Default_Field_Status  where Sch_Id =" + AddressFields.SchoolID + " and Status='A')as SSADFS on SSAF.Add_Id=SSADFS.Add_Id left outer join(select * from Student_Setup_Default_Field where Def_Field_Id=" + AddressFields.FieldId + ") SSDF on SSAF.Def_Field_Id=SSDF.Def_Field_Id WHERE SSAF.Def_Field_Id=" + AddressFields.FieldId + " and (SSADFS.Field_View='V_E' or SSADFS.Field_View='E') order by SSAF.Add_Id").Tables[0];


            //ConList = DataConnection.ConnectionPath.Getdata("select Cont_Id_List from Student_Setup_Additional_Contact_List where Sch_Id=" + AddressFields.SchoolID + " and Def_Field_Id=" + AddressFields.FieldId + "").Tables[0];

            //if (ConList.Rows.Count > 0)
            //{
            //    contactidlist = ConList.Rows[0][0];
            //    dtadditional = DataConnection.ConnectionPath.Getdata("select SSADCF.Cont_Id as FieldId, SSADCF.Field_Name as FieldName,SSADCF.Field_Type as FieldType,SSACS.Status as Status,SSADCF.Status  as IsMandatory,SSCF.CustomField as Default_Custom,SSACS.Field_View from Student_Setup_Additional_Default_Contact_Field SSADCF  left outer join (select * from Student_Setup_Additional_Contact_Status where Sch_Id=" + AddressFields.SchoolID + " and Status='A') as SSACS on SSADCF.Cont_Id=SSACS.Cont_Id left outer join Student_Setup_Additional_Contact_List SSACL on SSADCF.Cont_Id in (" + contactidlist.ToString() + ") left outer join (select * from Student_Setup_Custom_Field where extrafield_id=" + AddressFields.FieldId + ") SSCF on SSACL.Def_Field_Id=SSCF.extrafield_id where SSADCF.Cont_Id in (" + contactidlist.ToString() + ") and (SSACS.Field_View='V_E' or SSACS.Field_View='E')").Tables[0];

            //    dtdefault.Merge(dtadditional);
            //}

            return dtdefault;

        }


        //add student
        public int AddStudent(StudentBL STBL)
        {

            int id = 0;
            return id;

        }

        public void AddStudentdata(StudentBL STBL)
        {



        }

        public System.Data.DataTable populateadditionalfield(StudentBL AddressFields)
        {

            DataTable dtadditional = null;
            DataTable dtHomeAddress = null;
            // DataTable ConList = null;
            // object contactidlist = "";
            //  ConList = DataConnection.ConnectionPath.Getdata("select Cont_Id_List from Student_Setup_Additional_Contact_List where Sch_Id=" + AddressFields.SchoolID + "").Tables[0];

            //if (ConList.Rows.Count > 0)
            // {
            // contactidlist = ConList.Rows[0][0];
            // dtadditional = DataConnection.ConnectionPath.Getdata("select SSADCF.Cont_Id as FieldId, SSADCF.Field_Name as FieldName,SSADCF.Field_Type as FieldType,SSACS.Status as Status,SSADCF.Status  as IsMandatory,SSCF.CustomField as Default_Custom,SSACS.Field_View from Student_Setup_Additional_Default_Contact_Field SSADCF  left outer join (select * from Student_Setup_Additional_Contact_Status where Sch_Id=" + AddressFields.SchoolID + " and Status='A') as SSACS on SSADCF.Cont_Id=SSACS.Cont_Id left outer join Student_Setup_Additional_Contact_List SSACL on SSADCF.Cont_Id in (" + contactidlist.ToString() + ") left outer join (select * from Student_Setup_Custom_Field where extrafield_id=" + AddressFields.FieldId + ") SSCF on SSACL.Def_Field_Id=SSCF.extrafield_id where SSADCF.Cont_Id in (" + contactidlist.ToString() + ") and (SSACS.Field_View='V_E' or SSACS.Field_View='E') order by SSADCF.Cont_Id").Tables[0];

            dtadditional = DataConnection.ConnectionPath.Getdata("select SSADCF.Cont_Id as FieldId,SSADCF.Field_Name as FieldName,SSADCF.Field_Type as FieldType,SSACS.Status as Status,SSADCF.Status  as IsMandatory,'C' as Default_Custom,SSACS.Field_View from Student_Setup_Additional_Contact_Status SSACS inner join Student_Setup_Additional_Default_Contact_Field SSADCF on SSACS.Cont_Id =SSADCF.Cont_Id  where Sch_Id=" + AddressFields.SchoolID + " and SSACS.Status='A' and (Field_View='V_E' or Field_View='E') order by SSADCF.Cont_Id").Tables[0];

            DataRow dr = dtadditional.NewRow();
            dr["FieldType"] = 2;

            dtadditional.Rows.Add(dr);
            dtHomeAddress = DataConnection.ConnectionPath.Getdata("select SSAF.Add_Id as FieldId,SSAF.Field_name as FieldName,SSAF.Field_Type as FieldType,SSADFS.Status as Status,SSAF.Status as IsMandatory,'C' as Default_Custom,SSADFS.Field_View from Student_Setup_Address_Field SSAF left outer join(select * from Student_Setup_Address_Default_Field_Status  where Sch_Id =" + AddressFields.SchoolID + " and Status='A')as SSADFS on SSAF.Add_Id=SSADFS.Add_Id left outer join(select * from Student_Setup_Default_Field where Def_Field_Id=" + AddressFields.HomeFieldId + ") SSDF on SSAF.Def_Field_Id=SSDF.Def_Field_Id WHERE SSAF.Def_Field_Id=" + AddressFields.HomeFieldId + " and (SSADFS.Field_View='V_E' or SSADFS.Field_View='E') order by SSAF.Add_Id").Tables[0];

            dtadditional.Merge(dtHomeAddress);

            // }

            return dtadditional;

        }

        //---------------------------------------------------------------------

        public DataSet populatecontactrelationship()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from person_relationship");
            return ds;
        }

        public int StudentAdd(StudentBL student)
        {
            if (server == "MSSQLServer")
            {

                DataSet ds = null;
                object SchoolId = "";
                ds = DataConnection.ConnectionPath.Getdata("exec getMax std_id,student_school");
                String strStudentAdd = "INSERT into student_school(std_id,sch_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.SchoolID + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);

                SchoolId = DataConnection.ConnectionPath.Getdata("select MAX(std_id) from student_school").Tables[0].Rows[0][0];

                return Convert.ToInt32(SchoolId);
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                object SchoolId = "";
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('std_id','student_school')");
                String strStudentAdd = "INSERT into student_school(std_id,sch_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.SchoolID + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);

                SchoolId = DataConnection.ConnectionPath.Getdata("select MAX(std_id) from student_school").Tables[0].Rows[0][0];

                return Convert.ToInt32(SchoolId);
            }
            else
            {

                object SchoolId = "";
                return Convert.ToInt32(SchoolId);
            }

        }

        public void insertintostudentdetails(StudentBL student)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax std_details_id,student_details");
                String strStudentAdd = "INSERT into student_details(std_details_id,std_id,field_category,field_id,value,cat_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.StudentID + ",'" + student.FieldCategory + "'," + student.FieldId + ",'" + student.Value.Replace("'", "''") + "'," + student.CatId + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('std_details_id','student_details')");
                String strStudentAdd = "INSERT into student_details(std_details_id,std_id,field_category,field_id,value,cat_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.StudentID + ",'" + student.FieldCategory + "'," + student.FieldId + ",'" + student.Value.Replace("'", "''") + "'," + student.CatId + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);
            }

        }

        public void insertintostudentadddressdetails(StudentBL student)
        {

            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax std_add_id,Student_Adddress_Details");
                String strStudentAdd = "INSERT into Student_Adddress_Details(std_add_id,std_id,add_id,add_value,def_field_id,cat_id,sch_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.StudentID + "," + student.AddId + ",'" + student.Value.Replace("'", "''") + "'," + student.FieldId + "," + student.CatId + "," + student.SchoolID + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('std_add_id','Student_Adddress_Details')");
                String strStudentAdd = "INSERT into Student_Adddress_Details(std_add_id,std_id,add_id,add_value,def_field_id,cat_id,sch_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.StudentID + "," + student.AddId + ",'" + student.Value.Replace("'", "''") + "'," + student.FieldId + "," + student.CatId + "," + student.SchoolID + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);
            }

        }


        public void insertintostudentadditionalcontact(StudentBL student)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax Std_Add_Cont_Id,Student_Additional_Contact");
                String strStudentAdd = "INSERT into Student_Additional_Contact(Std_Add_Cont_Id,Cat_Id,Std_Id,Cont_Id,Value,Contact_Set,sch_Id,Cont_set_Id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.CatId + "," + student.StudentID + "," + student.FieldId + ",'" + student.Value.Replace("'", "''") + "'," + student.Set + "," + student.SchoolID + "," + student.ContSetId + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('Std_Add_Cont_Id','Student_Additional_Contact')");

                String strStudentAdd = "INSERT into Student_Additional_Contact(Std_Add_Cont_Id,Cat_Id,Std_Id,Cont_Id,Value,Contact_Set,sch_Id,Cont_set_Id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.CatId + "," + student.StudentID + "," + student.FieldId + ",'" + student.Value.Replace("'", "''") + "'," + student.Set + "," + student.SchoolID + "," + student.ContSetId + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);
            }

        }




        //*********************************photo save(saikat-14/10/2009)***********************************

        public void savestudentphoto(StudentBL student)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                object SchoolId = "";
                ds = DataConnection.ConnectionPath.Getdata("exec getMax std_pic_id,student_picture");
                String strStudentAdd = "INSERT into student_picture values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.StudentID + "," + student.SchoolID + ",'" + student.PhotoUrl + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);

            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                object SchoolId = "";
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('std_pic_id','student_picture')");
                String strStudentAdd = "INSERT into student_picture values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.StudentID + "," + student.SchoolID + ",'" + student.PhotoUrl + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);

            }








        }

        //*********************************photo save(saikat-14/10/2009)***********************************


        public int countsetfields(StudentBL fieldcount)
        {
            object count;
            count = DataConnection.ConnectionPath.Getdata("select COUNT(*) from Student_Additional_Contact where Std_Id=" + fieldcount.StudentID + " and sch_Id=" + fieldcount.SchoolID + " group by Contact_Set").Tables[0].Rows[0][0];
            return Convert.ToInt32(count);
        }

        //--------------------------------------------------------------------------------------------------

        public System.Data.DataTable getvaccinationfields(StudentBL SchoolSpCat)
        {
            DataTable dtdefault = null;
            //DataTable dtcustom = null;
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Setup_FormLayout_Order where Sch_Id=" + SchoolSpCat.SchoolID + " and Cat_Id=" + SchoolSpCat.CatId + "").Tables[0];
            //if (dt.Rows.Count == 0)
            //{
            dtdefault = DataConnection.ConnectionPath.Getdata("select Vac_FieldId as FieldId,Field_Name as FieldName,Field_Type as FieldType,DefaultFields as Default_Custom from Student_Setup_Vaccination_Record").Tables[0];
            //dtcustom = DataConnection.ConnectionPath.Getdata("select SSCF.extrafield_id as FieldId, SSCF.field_name as FieldName,SSCF.field_type_id as FieldType,SSCF.Status as Status,SSCF.CustomField as Default_Custom,SSCF.CustomField as IsMandatory from Student_Setup_Custom_Field SSCF where Category_Id=" + SchoolSpCat.CatId + " and sch_id=" + SchoolSpCat.SchoolID + " and SSCF.Status!='I' and (SSCF.Field_View='V_E' or SSCF.Field_View='E')").Tables[0];

            //dtdefault.Merge(dtcustom);
            return dtdefault;

            //}
            //else
            //{
            //    dtdefault = DataConnection.ConnectionPath.Getdata("select SSDF.Def_Field_Id as FieldId,SSDF.field_name as FieldName,SSDF.Field_Type as FieldType,SSDFS.Status as Status,SSDF.DefaultField as Default_Custom,SSDF.Status as IsMandatory,SSFO.* from Student_Setup_Default_Field SSDF left outer join(select * from Student_Setup_Default_Field_Status where School_Id =" + SchoolSpCat.SchoolID + " and Status='A')as SSDFS on SSDF.Def_Field_Id=SSDFS.Default_Field_Id left outer JOIN Student_Setup_FormLayout_Order SSFO on SSDF.Def_Field_Id=SSFO.Field_Id AND SSDF.DefaultField=SSFO.FL_FieldType AND SSFO.Sch_Id=" + SchoolSpCat.SchoolID + " WHERE SSDF.Cat_Id=" + SchoolSpCat.CatId + " and (SSDFS.Status!='I' or SSDFS.Status IS NULL) and (SSDFS.Field_View='V_E' or SSDFS.Field_View='E') and SSDF.Def_Field_Id not in(192,193,194,195,196,197,198,199,200,201,115,168,116,169,170,171,119,142,141,143,120) ORDER BY SSFO.Sort_Order").Tables[0];

            //    dtcustom = DataConnection.ConnectionPath.Getdata("select distinct SSCF.extrafield_id as FieldId, SSCF.field_name as FieldName,SSCF.field_type_id as FieldType,SSCF.Status as Status,SSCF.CustomField as Default_Custom,SSFO.* from Student_Setup_Custom_Field SSCF left outer join Student_Setup_FormLayout_Order SSFO on SSCF.extrafield_id=SSFO.Field_Id AND SSCF.Category_Id=SSFO.Cat_Id and SSCF.sch_id=SSFO.Sch_Id and SSCF.CustomField=SSFO.FL_FieldType where Category_Id=" + SchoolSpCat.CatId + " and SSCF.sch_id=" + SchoolSpCat.SchoolID + " and SSCF.Status!='I' and (SSCF.Field_View='V_E' or SSCF.Field_View='E') order by SSFO.Sort_Order").Tables[0];
            //    dtdefault.Merge(dtcustom);
            //    DataView myDataView = dtdefault.DefaultView;
            //    myDataView.Sort = "Sort_Order";

            //    return myDataView.ToTable();
            //    //return dtdefault;


            //}
        }

        public System.Data.DataTable getmedicalinfofields(StudentBL SchoolSpCat)
        {
            DataTable dtdefault = null;
            //DataTable dtcustom = null;
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Setup_FormLayout_Order where Sch_Id=" + SchoolSpCat.SchoolID + " and Cat_Id=" + SchoolSpCat.CatId + "").Tables[0];
            //if (dt.Rows.Count == 0)
            //{
            dtdefault = DataConnection.ConnectionPath.Getdata("select Med_Info_Id as FieldId,Field_Name as FieldName,Field_Type as FieldType,DefaultField as Default_Custom from Student_Setup_Medical_Information").Tables[0];
            //dtcustom = DataConnection.ConnectionPath.Getdata("select SSCF.extrafield_id as FieldId, SSCF.field_name as FieldName,SSCF.field_type_id as FieldType,SSCF.Status as Status,SSCF.CustomField as Default_Custom,SSCF.CustomField as IsMandatory from Student_Setup_Custom_Field SSCF where Category_Id=" + SchoolSpCat.CatId + " and sch_id=" + SchoolSpCat.SchoolID + " and SSCF.Status!='I' and (SSCF.Field_View='V_E' or SSCF.Field_View='E')").Tables[0];

            //dtdefault.Merge(dtcustom);
            return dtdefault;

            //}
            //else
            //{
            //    dtdefault = DataConnection.ConnectionPath.Getdata("select SSDF.Def_Field_Id as FieldId,SSDF.field_name as FieldName,SSDF.Field_Type as FieldType,SSDFS.Status as Status,SSDF.DefaultField as Default_Custom,SSDF.Status as IsMandatory,SSFO.* from Student_Setup_Default_Field SSDF left outer join(select * from Student_Setup_Default_Field_Status where School_Id =" + SchoolSpCat.SchoolID + " and Status='A')as SSDFS on SSDF.Def_Field_Id=SSDFS.Default_Field_Id left outer JOIN Student_Setup_FormLayout_Order SSFO on SSDF.Def_Field_Id=SSFO.Field_Id AND SSDF.DefaultField=SSFO.FL_FieldType AND SSFO.Sch_Id=" + SchoolSpCat.SchoolID + " WHERE SSDF.Cat_Id=" + SchoolSpCat.CatId + " and (SSDFS.Status!='I' or SSDFS.Status IS NULL) and (SSDFS.Field_View='V_E' or SSDFS.Field_View='E') and SSDF.Def_Field_Id not in(192,193,194,195,196,197,198,199,200,201,115,168,116,169,170,171,119,142,141,143,120) ORDER BY SSFO.Sort_Order").Tables[0];

            //    dtcustom = DataConnection.ConnectionPath.Getdata("select distinct SSCF.extrafield_id as FieldId, SSCF.field_name as FieldName,SSCF.field_type_id as FieldType,SSCF.Status as Status,SSCF.CustomField as Default_Custom,SSFO.* from Student_Setup_Custom_Field SSCF left outer join Student_Setup_FormLayout_Order SSFO on SSCF.extrafield_id=SSFO.Field_Id AND SSCF.Category_Id=SSFO.Cat_Id and SSCF.sch_id=SSFO.Sch_Id and SSCF.CustomField=SSFO.FL_FieldType where Category_Id=" + SchoolSpCat.CatId + " and SSCF.sch_id=" + SchoolSpCat.SchoolID + " and SSCF.Status!='I' and (SSCF.Field_View='V_E' or SSCF.Field_View='E') order by SSFO.Sort_Order").Tables[0];
            //    dtdefault.Merge(dtcustom);
            //    DataView myDataView = dtdefault.DefaultView;
            //    myDataView.Sort = "Sort_Order";

            //    return myDataView.ToTable();
            //    //return dtdefault;


            //}
        }

        public void insertintostudentmedicalinformationdetails(StudentBL student)
        {

            if (server == "MSSQLServer")
            {

                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax Id,Student_Medical_Information_Details");


                String strStudentEnrollment = "INSERT into Student_Medical_Information_Details(Id,Std_Id,Sch_Id,Value,Med_Info_Id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.StudentID + "," + student.SchoolID + ",'" + student.Value.Replace("'", "''") + "'," + student.MedInfoId + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentEnrollment);

            }
        }

        public void insertintostudentimmunizationdetails(StudentBL student)
        {

            if (server == "MSSQLServer")
            {

                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax Im_Id,Student_Immunization_Details");


                String strStudentEnrollment = "INSERT into Student_Immunization_Details(Im_Id,Type,Date,Comment,sch_id,std_id,Def_Field_Id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.MedInfoType + ",'" + student.MedInfoDate.Replace("'", "''") + "','" + student.Comments.Replace("'", "''") + "'," + student.SchoolID + "," + student.StudentID + "," + student.FieldId + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentEnrollment);

            }
        }

        public void insertintostudentnursevisitrecorddetails(StudentBL student)
        {

            if (server == "MSSQLServer")
            {

                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax Visit_Id,Student_Nurse_Visit_Record_Details");


                String strStudentEnrollment = "INSERT into Student_Nurse_Visit_Record_Details(Visit_Id,Visit_Date,Time_In,Time_Out,Reason,Result,Comment,Std_Id,Sch_Id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",'" + student.NurseVisitDate.Replace("'", "''") + "','" + student.NurseVisitTimeIn.Replace("'", "''") + "','" + student.NurseVisitTimeOut.Replace("'", "''") + "','" + student.NurseVisitReason.Replace("'", "''") + "','" + student.NurseVisitResult.Replace("'", "''") + "','" + student.Comments.Replace("'", "''") + "'," + student.StudentID + "," + student.SchoolID + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentEnrollment);

            }
        }

        public void insertintostudentvaccinationrecorddetails(StudentBL student)
        {

            if (server == "MSSQLServer")
            {

                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax Vac_Id,Student_Vaccination_Record_Details");


                String strStudentEnrollment = "INSERT into Student_Vaccination_Record_Details(Vac_Id,Std_Id,Sch_Id,Value,Vac_FieldId) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.StudentID + "," + student.SchoolID + ",'" + student.Value.Replace("'", "''") + "'," + student.FieldId + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentEnrollment);

            }
        }

        public void insertintostudentmedicalalert(StudentBL student)
        {

            if (server == "MSSQLServer")
            {

                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax stu_med_alert_id,student_medical_alert");


                String strStudentEnrollment = "INSERT into student_medical_alert(stu_med_alert_id,stu_id,med_alert_act_id,stu_med_alert_nm,life_threat_ind,last_updated) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + student.StudentID + "," + student.MedAlertActId + ",'" + student.Value.Replace("'", "''") + "'," + student.LifeThreatInd + ",'" + DateTime.Now.ToString() + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentEnrollment);

            }
        }

        public void insertintoenrollment(StudentBL student)
        {
            DataSet ds = null;

            ds = DataConnection.ConnectionPath.Getdata("exec getMax Std_Enr_Id,student_enrollment");


            String strStudentEnrollment = "INSERT into student_enrollment(Std_Enr_Id,Enr_Start_Date,Enr_End_Date,Std_Id,Sch_Id,Schyr_Id,Enr_Code_Id,Exit_Reason,Start_Condition) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",'" + student.EnrAttStartdate + "','" + student.EnrExitDate + "','" + student.StudentID + "'," + student.SchoolID + ",'" + student.EnrSchyrId + "','" + student.EnrExitCode.Replace("'", "''") + "','" + student.EnrExitreason.Replace("'", "''") + "','" + student.EnrStartCondition.Replace("'", "''") + "')";

            DataConnection.ConnectionPath.ExecuteCommand(strStudentEnrollment);


        }
        public DataTable Getstudentschoolenroll(StudentBL studentinfo)
        {

            DataTable dt = null;



            String strStudentEnrollment = "select student_enrollment.*,ENR.enr_cd_nm as enrollname  from student_enrollment inner join enrollment_codes ENR on student_enrollment.Enr_Code_Id = ENR.enr_cd_id where Std_Id = " + studentinfo.StudentID + " and Sch_Id= " + studentinfo.SchoolID + "";

            dt = DataConnection.ConnectionPath.Getdata(strStudentEnrollment).Tables[0];

            return dt;


        }

        public void UpdateEnrollment(StudentBL student)
        {


            string updateenroll = "Update student_enrollment set Enr_Start_Date ='" + student.EnrAttStartdate.Replace("'", "''") + "',Enr_End_Date ='" + student.EnrEndDate.Replace("'", "''") + "',Enr_Code_Id ='" + student.EnrExitCode.Replace("'", "''") + "',Exit_Reason ='" + student.EnrExitreason.Replace("'", "''") + "',Start_Condition ='" + student.EnrStartCondition.Replace("'", "''") + "' where Std_Enr_Id ='" + student.EnrollId + "'";

            DataConnection.ConnectionPath.ExecuteCommand(updateenroll);


        }

        public void DeleteEnrollment(StudentBL student)
        {


            string updateenroll = "Delete from student_enrollment where Std_Enr_Id ='" + student.EnrollId + "'";

            DataConnection.ConnectionPath.ExecuteCommand(updateenroll);


        }

        //special ed and gifted starts here

        public System.Data.DataTable PopulateSpecialField(StudentBL AddressFields)
        {

            DataTable dtspecial = null;

            dtspecial = DataConnection.ConnectionPath.Getdata("select SSADCF.Spl_Id as FieldId,SSADCF.Field_Name as FieldName,SSADCF.Field_Type as FieldType,SSACS.Status as Status,SSADCF.Status as IsMandatory,SSACS.Spl_Field_View from Student_SetUp_Programs_Default_Special_Education_Status SSACS inner join Student_SetUp_Programs_Default_Special_Education SSADCF on SSACS.Spl_Id =SSADCF.Spl_Id  where sch_id=" + AddressFields.SchoolID + " and SSACS.Status='A' and (Spl_Field_View='V_E' or Spl_Field_View='E') order by SSADCF.Spl_Id").Tables[0];

            return dtspecial;

        }

        public System.Data.DataTable PopulateGiftField(StudentBL AddressFields)
        {

            DataTable dtspecial = null;

            dtspecial = DataConnection.ConnectionPath.Getdata("select SSADCF.Gft_Id as FieldId,SSADCF.Field_Name as FieldName,SSADCF.Field_Type as FieldType,SSACS.Status as Status,SSADCF.Status as IsMandatory,SSACS. Gft_Field_View from Student_Setup_Programs_Gifted_Default_Field_Status SSACS inner join Student_Setup_Programs_Gifted_Default_Field SSADCF on SSACS.Gft_Id =SSADCF.Gft_Id  where sch_id=" + AddressFields.SchoolID + " and SSACS.Status='A' and (Gft_Field_View='V_E' or Gft_Field_View='E')  order by SSADCF.Gft_Id").Tables[0];

            return dtspecial;

        }


        public DataSet PopulateEvaluationType()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from evaluation_type");
            return ds;
        }
        public DataSet PopulateDisabilities()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from disabilties");
            return ds;
        }
        public DataSet PopulateSpclEduStatus()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from Special_Education_Status");
            return ds;
        }
        public DataSet PopulateSpclEduExistStatus()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from Special_Education_Exist_Status");
            return ds;
        }
        public DataSet PopulateGiftedCategory()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from Gifted_Category");
            return ds;
        }

        public void insertspecialvalue(StudentBL student)
        {
            DataSet ds = null;

            ds = DataConnection.ConnectionPath.Getdata("exec getMax Std_Prog_Spl_Id,Student_SetUp_Programs_Special_Education_Details");


            String strStudentEnrollment = "INSERT into Student_SetUp_Programs_Special_Education_Details(Std_Prog_Spl_Id,Spl_Id,Cat_Id,Std_Id,Value,Sch_Id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",'" + student.FieldId + "','" + student.CatId + "','" + student.StudentID + "','" + student.Value + "'," + student.SchoolID + ")";

            DataConnection.ConnectionPath.ExecuteCommand(strStudentEnrollment);


        }

        public void insertgiftvalue(StudentBL student)
        {
            DataSet ds = null;




            ds = DataConnection.ConnectionPath.Getdata("exec getMax Std_Prog_Gft_Id,Student_Setup_Programs_Gifted_Details");


            String strStudentEnrollment = "INSERT into Student_Setup_Programs_Gifted_Details(Std_Prog_Gft_Id,Gft_Id,Cat_Id,value,sch_id,std_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",'" + student.FieldId + "','" + student.CatId + "','" + student.Value + "','" + student.SchoolID + "'," + student.StudentID + ")";

            DataConnection.ConnectionPath.ExecuteCommand(strStudentEnrollment);


        }

        //*********************05/05/2010
        public System.Data.DataTable VaccinationfieldsWithValue(StudentBL SchoolSpCat)
        {
            DataTable dtdefault = null;
            //DataTable dtcustom = null;
            
            dtdefault = DataConnection.ConnectionPath.Getdata("select vr.Vac_FieldId as FieldId,vr.Field_Name as FieldName,vr.Field_Type as FieldType,vr.DefaultFields as Default_Custom, vrd.Value as Value from Student_Setup_Vaccination_Record as vr,Student_Vaccination_Record_Details as vrd where vr.Vac_FieldId=vrd.Vac_FieldId and vrd.Sch_Id=" + SchoolSpCat.SchoolID + " and vrd.Std_Id=" + SchoolSpCat.StudentID + "").Tables[0];
           
            return dtdefault;

            //}
           
        }
         


    }
}
